using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VWBFEHL
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                default:
                case "TST":
                case "YGR":
                case "SXN":
                case "UBT":
                case "JLT":
                    #region
                    dwReturn["WBFMANDANT"] = "00";
                    dwReturn["WBFRWKRS"] = "  ";
                    dwReturn["WBFRWLFD"] = "000000";
                    dwReturn["WBFRWPOS"] = "0001";
                    dwReturn["WBFVERSION"] = "1";
                    dwReturn["WBFFPOS"] = "1";
                    dwReturn["WBFFSCHL"] = "   ";
                    dwReturn["WBFLGVON"] = "-1000";
                    dwReturn["WBFLGBIS"] = "-1000";
                    dwReturn["WBFBRTVON"] = "0";
                    dwReturn["WBFBRTBIS"] = "0";
                    dwReturn["WBFVGUETKZ"] = " ";
                    dwReturn["WBFMARKART"] = " ";
                    dwReturn["WBFSEITE"] = "V";
                    dwReturn["WBFRICHT"] = "V";
                    dwReturn["WBFUMRECH"] = " ";
                    dwReturn["WBFZENSUR"] = " ";
                    dwReturn["WBFZENSURKZ"] = " ";
                    dwReturn["WBFRWPOST"] = "0";
                    dwReturn["WBFQPKT"] = "0";
                    dwReturn["WBFSTATKZ"] = " ";
                    dwReturn["WBFBAHN"] = "00";
                    dwReturn["WBFGWT"] = " ";
                    dwReturn["WBFVGUET"] = "0";
                    dwReturn["WBFFEART"] = "V";
                    dwReturn["WBFNETTOKZ"] = " ";
                    dwReturn["WBFTYP"] = " ";
                    dwReturn["WBFFILLER"] = "  00        ";
                    #endregion
                    break;
            }
        }
        public void InsertWBFEHL(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            or1 = OracleDB.CreateCommand();
            switch (app.company)
            {
                default:
                case "TST":
                case "YGR":
                case "SXN":
                case "UBT":
                case "JLT":
                    or1.CommandText = "INSERT INTO WBFEHL ( WBFMANDANT,WBFRWKRS,WBFRWLFD,WBFRWPOS,WBFVERSION,WBFFPOS,WBFFSCHL,WBFLGVON,WBFLGBIS,WBFBRTVON,WBFBRTBIS,WBFVGUETKZ,WBFMARKART,WBFSEITE,WBFRICHT,WBFUMRECH,WBFZENSUR,WBFZENSURKZ,WBFRWPOST,WBFQPKT,WBFSTATKZ,WBFBAHN,WBFGWT,WBFVGUET,WBFFEART,WBFNETTOKZ,WBFTYP,WBFFILLER)"
                   + " VALUES (:WBFMANDANT:,:WBFRWKRS:,:WBFRWLFD:,:WBFRWPOS:,:WBFVERSION:,:WBFFPOS:,:WBFFSCHL:,:WBFLGVON:,:WBFLGBIS:,:WBFBRTVON:,:WBFBRTBIS:,:WBFVGUETKZ:,:WBFMARKART:,:WBFSEITE:,:WBFRICHT:,:WBFUMRECH:,:WBFZENSUR:,:WBFZENSURKZ:,:WBFRWPOST:,:WBFQPKT:,:WBFSTATKZ:,:WBFBAHN:,:WBFGWT:,:WBFVGUET:,:WBFFEART:,:WBFNETTOKZ:,:WBFTYP:,:WBFFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                    //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryWBFEHL(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0: SResult = QWBFEHL00(dwData); break;
                case 2: SResult = QWBFEHL02(dwData); break;
                case 3: SResult = QWBFEHL03(dwData); break;
                case 99: SResult = QWBFEHL99(dwData); break;
            }
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QWBFEHL00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM WBFEHL  WHERE ROWNUM<2"
                            + " ORDER BY WBFMANDANT,WBFRWKRS,WBFRWLFD,WBFRWPOS,WBFVERSION, WBFFPOS";

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QWBFEHL03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "select B.WBBEINDAT,trim(b.wbbart) || '-' || trim(b.wbbfarbe) as article,B.WBBSTKNR, "
               + " a.wbffschl AS DEFECT,(select trim(substr(sddaten, 2, 20)) from sddat where sdsa = '0267' "
               + " and substr(sdkey, 1, 5) = a.wbfmandant || a.wbffschl) as WBFFSCHL, "
               + " A.WBFLGVON*0.001 AS DEFMTR,B.WBBWARSCH,b.wbbwbmlfdnr AS WBBWBMLFDNR, "
               + " DECODE(B.WBBEINDAT, 0, 0, b.wbbsstklg) * 0.001 as PCEMTR, "
               + " (SELECT WBFFSCHL FROM (select DISTINCT A.WBFMANDANT,A.WBFRWKRS,a.wbfrwlfd,a.wbfrwpos, "
               + " a.wbffschl||'-'||(select trim(substr(sddaten, 2, 20)) from sddat where sdsa = '0267' "
               + " and substr(sdkey, 1, 5) = a.wbfmandant || a.wbffschl) as WBFFSCHL, "
               + " (CASE WHEN A.WBFLGVON < 1000 THEN 0 ELSE A.WBFLGVON END) AS STARTMTR, "
               + " lead(a.WBFLGVON,1,DECODE(B.WBBEINDAT, 0, 0, b.wbbsstklg))  "
               + " over(partition by A.WBFMANDANT, A.WBFRWKRS, a.wbfrwlfd, a.wbfrwpos  "
               + " order by A.WBFMANDANT, A.WBFRWKRS, a.wbfrwlfd, a.wbfrwpos, a.wbfversion, a.wbffpos) as ENDMTR "
               + "  from wbfehl a, wbbst b  "
               + " where A.WBFFSCHL < '900' AND A.WBFFSCHL > '500' "
               + " and a.wbfmandant = b.wbbmandant and a.wbfrwkrs = b.wbbrwkrs "
               + " and a.wbfrwlfd = b.wbbrwlfd and a.wbfrwpos = b.wbbpos) X  "
               + " WHERE X.WBFMANDANT = A.WBFMANDANT AND X.WBFRWKRS = A.WBFRWKRS "
               + " AND X.wbfrwlfd = a.wbfrwlfd AND X.wbfrwpos = a.wbfrwpos "
               + " AND X.STARTMTR < A.WBFLGVON AND X.ENDMTR > A.WBFLGVON ) AS STAFF "
               + " from wbfehl a, wbbst b "
               + " where A.WBFFSCHL >'A' and a.wbfmandant = b.wbbmandant "
               + " and a.wbfrwkrs = b.wbbrwkrs and a.wbfrwlfd = b.wbbrwlfd "
               + " and a.wbfrwpos = b.wbbpos "
               + " AND TRIM(b.wbbart) || '-' || TRIM(b.wbbfarbe) LIKE '" + dwData["WBBART"].ToString() + "%' "
               + " AND B.WBBEINDAT >= '" + dwData["WBBEINDAT"].ToString() + "' AND B.WBBEINDAT <= '" + dwData["WBBFLZZUGDAT"].ToString() + "' "
               + " order by 1,2,3,DEFMTR ";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QWBFEHL99(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM WBFEHL WHERE  ROWNUM<50 "
                            + " ORDER BY WBFMANDANT,WBFRWKRS,WBFRWLFD,WBFRWPOS,WBFVERSION, WBFFPOS";

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QWBFEHL01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.* FROM WBFEHL M "
                 + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION: AND WBFFPOS =:WBFFPOS:"
                             + " ORDER BY WBFMANDANT,WBFRWKRS,WBFRWLFD,WBFRWPOS,WBFVERSION, WBFFPOS";

            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QWBFEHL02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.* FROM WBFEHL M "
                + " WHERE WBFMANDANT=:WBFMANDANT: AND WBFRWKRS=:WBFRWKRS: AND WBFRWLFD=:WBFRWLFD: AND WBFRWPOS=:WBFRWPOS: "
                            + " ORDER BY WBFMANDANT,WBFRWKRS,WBFRWLFD,WBFRWPOS,WBFVERSION, WBFFPOS";

            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }


        public DataSet QWBFEHL04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT SUBSTR(SDDATEN,70,2) AS V1,SUBSTR(SDDATEN,72,2) AS V2,SUBSTR(SDDATEN,74,2) AS V3,SUBSTR(SDDATEN,76,2) AS V4,SUBSTR(SDDATEN,78,2) AS V5 ,M.* FROM WBFEHL M "
                + " JOIN SDDAT ON SDSA='0267' AND SUBSTR(SDKEY,3,3)=WBFFSCHL AND SUBSTR(SDKEY,1,2)=WBFMANDANT  "
                 + " WHERE    WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION:"
                 + dwData["WBFFSCHL"].ToString()
            + " ORDER BY WBFMANDANT,WBFRWKRS,WBFRWLFD,WBFRWPOS,WBFVERSION, WBFFPOS";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }

        public void DWFE01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand(); 
            or1.CommandText = "DELETE FROM WBFEHL " 
                + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION: AND WBFFPOS =:WBFFPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
            Connect2Proxy(or1);
        }
        public void DWFE02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM WBFEHL "
                + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DWFE03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM WBFEHL "
                + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DWFE04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM WBFEHL "
                + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS:  AND WBFLGVON<0";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UpdateWBFEHL(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            if (option == 0)
            {
                switch (app.company)
                {
                    default:
                    case "TST":
                    case "YGR":
                    case "SXN":
                    case "UBT":
                    case "JLT":
                        UWEF01(dwData);
                        break;
                }
            }
        }
        public void UWEF01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE WBFEHL SET WBFMANDANT =:WBFMANDANT:,WBFRWKRS =:WBFRWKRS:,WBFRWLFD =:WBFRWLFD:,WBFRWPOS =:WBFRWPOS:,WBFVERSION =:WBFVERSION:,WBFFPOS =:WBFFPOS:,WBFFSCHL =:WBFFSCHL:,WBFLGVON =:WBFLGVON:,WBFLGBIS =:WBFLGBIS:,WBFBRTVON =:WBFBRTVON:,WBFBRTBIS =:WBFBRTBIS:,WBFVGUETKZ =:WBFVGUETKZ:,WBFMARKART =:WBFMARKART:,WBFSEITE =:WBFSEITE:,WBFRICHT =:WBFRICHT:,WBFUMRECH =:WBFUMRECH:,WBFZENSUR =:WBFZENSUR:,WBFZENSURKZ =:WBFZENSURKZ:,WBFRWPOST =:WBFRWPOST:,WBFQPKT =:WBFQPKT:,WBFSTATKZ =:WBFSTATKZ:,WBFBAHN =:WBFBAHN:,WBFGWT =:WBFGWT:,WBFVGUET =:WBFVGUET:,WBFFEART =:WBFFEART:,WBFNETTOKZ =:WBFNETTOKZ:,WBFTYP =:WBFTYP:,WBFFILLER =:WBFFILLER:"
                 + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION: AND WBFFPOS =:WBFFPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UWEFSHIFT(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE WBFEHL SET WBFMARKART =:WBFMARKART:,WBFRWPOST =:WBFRWPOST:,WBFVGUET =:WBFVGUET:,WBFFILLER=:WBFFILLER:"
                 + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION: AND WBFFPOS =:WBFFPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UWEFQPKT(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE WBFEHL SET  WBFQPKT=:WBFQPKT:"
                 + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION: AND WBFFPOS =:WBFFPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UWEF999(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE WBFEHL SET WBFFPOS =998"
                 + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION: AND WBFFPOS =:WBFFPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UWEFADD(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE WBFEHL SET WBFFPOS =WBFFPOS+1"
                 + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION: AND WBFFPOS =:WBFFPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UWEFRESET999(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE WBFEHL SET  WBFFPOS =:WBFFPOS:"
                 + " WHERE WBFMANDANT =:WBFMANDANT: AND WBFRWKRS =:WBFRWKRS: AND WBFRWLFD =:WBFRWLFD: AND WBFRWPOS =:WBFRWPOS: AND WBFVERSION =:WBFVERSION: AND WBFFPOS =998";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
    }
}
